/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.registration;

import com.registration.core.*;
import com.registration.membership.Member;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import javax.sql.DataSource;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

/**
 * @date May 7, 2012
 *
 * @author Ggutierrez
 */
public class RegistrationDao extends org.springframework.orm.hibernate3.support.HibernateDaoSupport {

    private int maxResults;
    private DataSource dataSource;

    public RegistrationDao() {
    }

    public int getMaxResults() {
        return maxResults;
    }

    public void setMaxResults(int maxResults) {
        this.maxResults = maxResults;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Object unique(List results) {
        return results == null || results.isEmpty() ? null : results.get(0);
    }

    public void attach(Object object) {
        getHibernateTemplate().refresh(object);
    }

    public List find(final String hql, final int maxResults) {
        return find(hql, null, maxResults);
    }

    public List find(final String hql, final Object[] args, final int maxResults) {
        HibernateCallback callback = new HibernateCallback() {

            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createQuery(hql);
                if (maxResults > 0) {
                    query.setMaxResults(maxResults);
                }
                if (args != null) {
                    for (int i = 0; i < args.length; i++) {
                        query.setParameter(i, args[i]);
                    }
                }
                return query.list();
            }
        };
        return (List) getHibernateTemplate().execute(callback);
    }

    private boolean isBlank(String value) {
        return value == null || value.trim().length() == 0;
    }

    public void store(Object object) {
        getHibernateTemplate().saveOrUpdate(object);
    }

    public Country loadCountry(Long id) {
        String hql = "select this from Country this where this.id = ?";
        return (Country) unique(getHibernateTemplate().find(hql, id));
    }

    public List<Country> findCountries() {
        String hql = "select this from Country this where this.shown = true ";
        List args = new ArrayList();

        return (List<Country>) find(hql, args.toArray(), -1);
    }

    public Category loadCategory(Long id) {
        String hql = "select this from Category this where this.id = ?";
        return (Category) unique(getHibernateTemplate().find(hql, id));
    }

    public List<Category> findCategories(Boolean restricted) {
        String hql = "select this from Category this where 1 = 1 ";
        List args = new ArrayList();

        if (restricted != null) {
            args.add(restricted.booleanValue());
            hql += " and this.restricted = ? ";
        }

        return (List<Category>) find(hql, args.toArray(), maxResults);
    }

    public List<Hotel> findHotels() {
        String hql = "select this from Hotel this";
        return (List<Hotel>) find(hql, maxResults);
    }

    public Person loadPerson(Long id) {
        String hql = "select this from Person this where this.id = ?";
        return (Person) unique(getHibernateTemplate().find(hql, id));
    }

    public List<Person> findPerson() {
        String hql = "select this from Person this where 1 = 1 ";
        List args = new ArrayList();

        return (List<Person>) find(hql, args.toArray(), maxResults);
    }

    public Attendee loadAttendee(Long id) {
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + "left join fetch this.lodging.hotel hotel "
                + "left join fetch this.paymentMethod paymentMethod "
                + "left join fetch this.roomPlan roomPlan "
                + " where this.id = ?";
        return (Attendee) unique(getHibernateTemplate().find(hql, id));
    }

    public Attendee loadAttendeeByMemberId(String memberId, Stage currentStage) {
        List args = new ArrayList();
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + "left join fetch this.lodging.hotel hotel " 
                + " where 1 = 1";
        
        if (!isBlank(memberId)) {
            args.add(memberId.trim());
            hql += " and this.memberId = ? ";
        }
        
        if (currentStage != null) {
            args.add(currentStage);
            hql += " and this.currentStage = ? ";
        }
        return (Attendee) unique(getHibernateTemplate().find(hql, memberId));
    }

    public Attendee loadAttendeeByConfirmation(String confirmationNumber, Stage currentStage) {
        List args = new ArrayList();
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + "left join fetch this.lodging.hotel hotel " 
                + " where 1 = 1";
        
        if (!isBlank(confirmationNumber)) {
            args.add(confirmationNumber.trim());
            hql += " and this.confirmationNumber = ? ";
        }
        
        if (currentStage != null) {
            args.add(currentStage);
            hql += " and this.currentStage = ? ";
        }
        return (Attendee) unique(getHibernateTemplate().find(hql, args.toArray()));
    }

    public List<Attendee> findAttendees(String firstName, String middleName, String lastName, String secondLastName, String memberId, Category category, Country residence, MemberVerification memberVerification, Stage currentStage) {
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + " where this.currentStage != 'REGISTRO_PRUEBA' ";
        List args = new ArrayList();

        if (!isBlank(firstName)) {
            args.add(firstName.trim());
            hql += " and this.firstName like ? ";
        }

        if (!isBlank(middleName)) {
            args.add(middleName.trim());
            hql += " and this.middleName like ? ";
        }

        if (!isBlank(lastName)) {
            args.add(lastName.trim());
            hql += " and this.lastName like ? ";
        }

        if (!isBlank(secondLastName)) {
            args.add(secondLastName.trim());
            hql += " and this.secondLastName like ? ";
        }

        if (!isBlank(memberId)) {
            args.add(memberId.trim());
            hql += " and this.memberId like ? ";
        }

        if (category != null) {
            args.add(category);
            hql += " and this.category = ? ";
        }

        if (residence != null) {
            args.add(residence);
            hql += " and this.residence = ? ";
        }

        if (memberVerification != null) {
            args.add(memberVerification);
            hql += " and this.memberVerification = ? ";
        }
        
        if (currentStage != null) {
            args.add(currentStage);
            hql += " and this.currentStage = ? ";
        }

        return (List<Attendee>) find(hql, args.toArray(), -1);
    }
    
    public List<Attendee> findAttendees(String firstName, String middleName, String lastName, String secondLastName, String memberId, String category, String residence, MemberVerification memberVerification, Stage currentStage, String branch) {
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + "left join fetch this.roomPlan roomPlan "
                + " where this.currentStage != 'REGISTRO_PRUEBA' ";
        List args = new ArrayList();

        if (!isBlank(firstName)) {
            args.add(firstName.trim());
            hql += " and this.firstName like ? ";
        }

        if (!isBlank(middleName)) {
            args.add(middleName.trim());
            hql += " and this.middleName like ? ";
        }

        if (!isBlank(lastName)) {
            args.add(lastName.trim());
            hql += " and this.lastName like ? ";
        }

        if (!isBlank(secondLastName)) {
            args.add(secondLastName.trim());
            hql += " and this.secondLastName like ? ";
        }

        if (!isBlank(memberId)) {
            args.add(memberId.trim());
            hql += " and this.memberId like ? ";
        }

        if (!isBlank(category)) {
            args.add("%".concat(category.trim().toUpperCase().concat("%")));
            hql += " and upper(this.category.description) like ? ";
        }

        if (!isBlank(residence)) {
            args.add("%".concat(residence.trim().toUpperCase().concat("%")));
            hql += " and upper(this.residence.name) like ? ";
        }

        if (memberVerification != null) {
            args.add(memberVerification);
            hql += " and this.memberVerification = ? ";
        }
        
        if (currentStage != null) {
            args.add(currentStage);
            hql += " and this.currentStage = ? ";
        }
        
        if (!isBlank(branch)) {
            args.add("%".concat(branch.trim().toUpperCase().concat("%")));
            hql += " and upper(this.branch) like ? ";
        }

        return (List<Attendee>) find(hql, args.toArray(), -1);
    }
    
    public List<Attendee> findLeaders() {
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + " where this.currentStage != 'REGISTRO_PRUEBA' "
                + "   and this.delegationLeader = true";
        
        return (List<Attendee>) find(hql, -1);
    }
    
    public List<Attendee> findHeads() {
        String hql = "select this from Attendee this left join fetch this.country country left join fetch this.residence residence "
                + "left join fetch this.category category "
                + " where this.currentStage != 'REGISTRO_PRUEBA' "
                + "   and this.lodging.roomLeader = true";
        
        return (List<Attendee>) find(hql, -1);
    }

    public Member loadMember(Long id) {
        String hql = "select this from Member this where this.id = ?";
        return (Member) unique(getHibernateTemplate().find(hql, id));
    }

    public Member loadMember(String memberId) {
        String hql = "select this from Member this where this.memberId = ?";
        return (Member) unique(getHibernateTemplate().find(hql, memberId));
    }

    public Long loadLastMember() {
        String hql = "select max(this.id) from Member this";
        return (Long) unique(getHibernateTemplate().find(hql));
    }

    public List<Member> findMembers() {
        String hql = "select this from Member this left outer join this.country country left outer join this.category category where 1 = 1 ";
        List args = new ArrayList();

        return (List<Member>) find(hql, args.toArray(), maxResults);
    }
    
    public List<PaymentMethod> findPaymentMethods() {
        String hql = "select this from PaymentMethod this "
                + " where this.active = true ";
        
        return (List<PaymentMethod>) find(hql, -1);
    }
    
    public List<RoomPlan> findRoomPlans() {
        String hql = "select this from RoomPlan this ";
        
        return (List<RoomPlan>) find(hql, -1);
    }

    public Long generateConfirmation() {
        Long confirmation = 0L;
        Calendar today = Calendar.getInstance();

        Long lastId = loadLastMember();
        String todayString = String.valueOf(today.get(Calendar.YEAR)) + String.valueOf(today.get(Calendar.MONTH) + String.valueOf(today.get(Calendar.DATE)));

        if (lastId != null) {
            if (lastId.compareTo(10L) < 0) {
                todayString += "00" + lastId.toString();
            } else {
                if (lastId.compareTo(100L) < 0) {
                    todayString += "0" + lastId.toString();
                }
            }
        } else {
            todayString += "001";
        }

        confirmation = Long.valueOf(todayString);

        return confirmation;
    }
}
